<?php
class ModelCatalogLgiProduct extends Model {
  public function getProduct($product_id) {
    //$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
    //lgis
    $query = $this->db->query("SELECT DISTINCT *, p.sort_order AS psort_order, CONCAT(vds.firstname, ' ', vds.lastname) AS vname, CONCAT(vds.city, ',', vds.address_1, ',', vds.postcode) AS address,(SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
    //lgie
    return $query->row;
  }

  public function getProducts($data = array()) {
    //$sql = "SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
    //lgis
    $sql = "SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id)"; 
    //lgie
    
    $sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 
    
    if (!empty($data['filter_name'])) {
      $sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
    }
    
    //lgis
    if (!empty($data['filter_sku'])) {
      $sql .= " AND LCASE(p.sku) LIKE '" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
    }
      
    if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
      $sql .= " AND vd.vendor = '" . (int)$data['filter_vendor'] . "'";
    }
      
    if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
      $sql .= " AND vds.vendor_id = '" . (int)$data['filter_vendor_name'] . "'";
    }
    //lgie

    if (!empty($data['filter_model'])) {
      $sql .= " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
    }

    if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
      $sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
    }

    if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
      $sql .= " AND p.quantity = '" . (int)$data['filter_quantity'] . "'";
    }

    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
      $sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
    }

    $sql .= " GROUP BY p.product_id";

    $sort_data = array(
      'pd.name',
      'p.model',
      //lgis
      'p.sku',
      'vds.vendor_id',
      //lgie
      'p.price',
      'p.quantity',
      'p.status',
      'p.sort_order'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY pd.name";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
      $sql .= " DESC";
    } else {
      $sql .= " ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getProductsByCategoryId($category_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "' ORDER BY pd.name ASC");

    return $query->rows;
  }

  public function getProductDescriptions($product_id) {
    $product_description_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_description_data[$result['language_id']] = array(
        'name'             => $result['name'],
        'description'      => $result['description'],
        'meta_title'       => $result['meta_title'],
        'meta_description' => $result['meta_description'],
        'meta_keyword'     => $result['meta_keyword'],
        'tag'              => $result['tag']
      );
    }

    return $product_description_data;
  }

  public function getProductCategories($product_id) {
    $product_category_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_category_data[] = $result['category_id'];
    }

    return $product_category_data;
  }

  public function getProductFilters($product_id) {
    $product_filter_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_filter_data[] = $result['filter_id'];
    }

    return $product_filter_data;
  }

  public function getProductAttributes($product_id) {
    $product_attribute_data = array();

    $product_attribute_query = $this->db->query("SELECT attribute_id FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' GROUP BY attribute_id");

    foreach ($product_attribute_query->rows as $product_attribute) {
      $product_attribute_description_data = array();

      $product_attribute_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

      foreach ($product_attribute_description_query->rows as $product_attribute_description) {
        $product_attribute_description_data[$product_attribute_description['language_id']] = array('text' => $product_attribute_description['text']);
      }

      $product_attribute_data[] = array(
        'attribute_id'                  => $product_attribute['attribute_id'],
        'product_attribute_description' => $product_attribute_description_data
      );
    }

    return $product_attribute_data;
  }

  public function getLgProductInfo($product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "logcenter_product WHERE product_id = '" . (int)$product_id . "' AND logcenter_id = '" . $this->user->getLP() . "'");

    return $query->row;
  }

  public function getLgProductOptionQty($product_id) {
    $product_opt_qty = M('logcenter_product_option_value lpov');
    $result = $product_opt_qty->join('product_option_value pov ON lpov.product_option_value_id = pov.product_option_value_id')
    ->join('option_value_description ovd on ovd.option_value_id = pov.option_value_id')
    ->field('lpov.product_option_value_id, lpov.quantity, ovd.name')
    ->where('ovd.language_id = ' . (int)$this->config->get('config_language_id') . ' AND lpov.logcenter_id = ' . (int)$this->user->getLP() . ' AND pov.product_id = ' . (int)$product_id . ' AND lpov.status = 1')
    ->select();

    return $result;
  }

  public function getProductOptions($product_id) {
    $product_option_data = array();

    $product_option_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_option` po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN `" . DB_PREFIX . "option_description` od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "'");

    foreach ($product_option_query->rows as $product_option) {
      $product_option_value_data = array();

      $product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "'");

      foreach ($product_option_value_query->rows as $product_option_value) {
        $product_option_value_data[] = array(
          'product_option_value_id' => $product_option_value['product_option_value_id'],
          'option_value_id'         => $product_option_value['option_value_id'],
          'quantity'                => $product_option_value['quantity'],
          'subtract'                => $product_option_value['subtract'],
          'price'                   => $product_option_value['price'],
          'price_prefix'            => $product_option_value['price_prefix'],
          'points'                  => $product_option_value['points'],
          'points_prefix'           => $product_option_value['points_prefix'],
          'weight'                  => $product_option_value['weight'],
          'weight_prefix'           => $product_option_value['weight_prefix']
        );
      }

      $product_option_data[] = array(
        'product_option_id'    => $product_option['product_option_id'],
        'product_option_value' => $product_option_value_data,
        'option_id'            => $product_option['option_id'],
        'name'                 => $product_option['name'],
        'type'                 => $product_option['type'],
        'value'                => $product_option['value'],
        'required'             => $product_option['required']
      );
    }

    return $product_option_data;
  }

  public function getProductImages($product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

    return $query->rows;
  }

  public function getProductDiscounts($product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' ORDER BY quantity, priority, price");

    return $query->rows;
  }

  public function getProductSpecials($product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' ORDER BY priority, price");

    return $query->rows;
  }

  public function getProductRewards($product_id) {
    $product_reward_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_reward_data[$result['customer_group_id']] = array('points' => $result['points']);
    }

    return $product_reward_data;
  }

  public function getProductDownloads($product_id) {
    $product_download_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_download WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_download_data[] = $result['download_id'];
    }

    return $product_download_data;
  }

  public function getProductStores($product_id) {
    $product_store_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_store_data[] = $result['store_id'];
    }

    return $product_store_data;
  }

  public function getProductLayouts($product_id) {
    $product_layout_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_layout_data[$result['store_id']] = $result['layout_id'];
    }

    return $product_layout_data;
  }

  public function getProductRelated($product_id) {
    $product_related_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");

    foreach ($query->rows as $result) {
      $product_related_data[] = $result['related_id'];
    }

    return $product_related_data;
  }

  public function getRecurrings($product_id) {
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_recurring` WHERE product_id = '" . (int)$product_id . "'");

    return $query->rows;
  }

  public function getTotalProducts($data = array()) {
    //$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
    //lgis
    $sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id)";
    //lgie
    
    $sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

    if (!empty($data['filter_name'])) {
      $sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
    }
    
    //lgis
    if (!empty($data['filter_sku'])) {
      $sql .= " AND LCASE(p.sku) LIKE '" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
    }
      
    if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
      $sql .= " AND vd.vendor = '" . (int)$this->db->escape($data['filter_vendor']) . "'";
    }
      
    if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
      $sql .= " AND vds.vendor_id = '" . (int)$this->db->escape($data['filter_vendor_name']) . "'";
    }
    //lgie

    if (!empty($data['filter_model'])) {
      $sql .= " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
    }

    if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
      $sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
    }

    if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
      $sql .= " AND p.quantity = '" . (int)$data['filter_quantity'] . "'";
    }

    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
      $sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
    }

    $query = $this->db->query($sql);

    return $query->row['total'];
  }

  public function getTotalProductsByTaxClassId($tax_class_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE tax_class_id = '" . (int)$tax_class_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByStockStatusId($stock_status_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE stock_status_id = '" . (int)$stock_status_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByWeightClassId($weight_class_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE weight_class_id = '" . (int)$weight_class_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByLengthClassId($length_class_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE length_class_id = '" . (int)$length_class_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByDownloadId($download_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_download WHERE download_id = '" . (int)$download_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByManufacturerId($manufacturer_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE manufacturer_id = '" . (int)$manufacturer_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByAttributeId($attribute_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_attribute WHERE attribute_id = '" . (int)$attribute_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByOptionId($option_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_option WHERE option_id = '" . (int)$option_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByProfileId($recurring_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_recurring WHERE recurring_id = '" . (int)$recurring_id . "'");

    return $query->row['total'];
  }

  public function getTotalProductsByLayoutId($layout_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_layout WHERE layout_id = '" . (int)$layout_id . "'");

    return $query->row['total'];
  }
  
  //lgis
  public function getVendorsByVendorId($vendor_id) {
    $data = array();
    $query = $this->db->query("SELECT *, CONCAT(v.firstname, ' ',v.lastname) AS vname, CONCAT(v.city, ',', v.address_1, ',', v.postcode) AS address FROM " . DB_PREFIX . "vendors v WHERE v.vendor_id = '" . (int)$vendor_id . "'");
    $data = $query->row;
        
    if ($query->num_rows) {
      $data = array_merge($data,array('country_name' => $this->getCountryName($data['country_id'])));
      if ($this->getZoneName($data['zone_id'])) {
        $data = array_merge($data,array('zone_name' => $this->getZoneName($data['zone_id'])));
      } else {
        $data = array_merge($data,array('zone_name' => array(0 => $this->language->get('text_none'))));
      }
    }
    return $data;
  }
  
  public function getVendors($data = array()) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "vendors v ORDER BY v.vendor_name");
    $vendors_data = $query->rows;
    return $vendors_data;
    $this->cache->set('vendors', $vendors_data);
  }
      
  public function getCountryName($country_id) {
    $country_name = array();
    $query = $this->db->query("SELECT name AS CountryName FROM " . DB_PREFIX . "country c WHERE c.country_id = '" . (int)$country_id . "'");
      foreach ($query->rows as $result) {
        $country_name[] = $result['CountryName'];
      }
    return $country_name;
  }
      
  public function getZoneName($zone_id) {
    $zone_name = array();
    $query = $this->db->query("SELECT name AS ZoneName FROM " . DB_PREFIX . "zone z WHERE z.zone_id = '" . (int)$zone_id . "'");
        
    foreach ($query->rows as $result) {
      $zone_name[] = $result['ZoneName'];
    }
    return $zone_name;
  }
      
  public function getCountry($data = array()) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "country c ORDER BY c.name");
    $country_data = $query->rows;
    return $country_data;
    $this->cache->set('product', $country_data);
  }
      
  public function getCourier($data = array()) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "courier cr ORDER BY cr.courier_name");
    $couriers_data = $query->rows;
    return $couriers_data;
    $this->cache->set('product', $couriers_data);
  }
      
  public function getTotalProductsByVendor($data = array(),$vendor_id) {
    $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id) WHERE vds.vendor_id = '" . (int)$vendor_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
        
    if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
      $sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_name'], 'UTF-8')) . "%'";
    }
    
    //lgis
    if (isset($data['filter_sku']) && !is_null($data['filter_sku'])) {
      $sql .= " AND LCASE(p.sku) LIKE '" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
    }
      
    if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
      $sql .= " AND vd.vendor = '" . (int)$this->db->escape($data['filter_vendor']) . "'";
    }
      
    if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
      $sql .= " AND vds.vendor_id = '" . (int)$this->db->escape($data['filter_vendor_name']) . "'";
    }
    //lgie

    if (isset($data['filter_model']) && !is_null($data['filter_model'])) {
      $sql .= " AND LCASE(p.model) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_model'], 'UTF-8')) . "%'";
    }
        
    if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
      $sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
    }
        
    if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
      $sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
    }
        
    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
      $sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
    }

    $query = $this->db->query($sql);
    return $query->row['total'];
  } 

  public function getTotalWaitingApprovalProduct($data = array()) {
    $query = $this->db->query("SELECT count(*) AS total FROM " . DB_PREFIX . "product p WHERE p.status = '5'");
    return $query->row['total'];
  }     
            
  public function getProductShippings($product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_shipping WHERE product_id = '" . (int)$product_id . "' ORDER BY product_shipping_id");
    return $query->rows;
  }
      
  public function getVendorData($vendor_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "vendors WHERE vendor_id = '" . (int)$this->db->escape($vendor_id) . "'");
    return $query->row;
  }

  public function checkLogInventory($product_ids) {
    $query = M('logcenter_product lp');
    $result = $query->join('logcenters l on l.logcenter_id=lp.logcenter_id')
    ->join('product_description pd on pd.product_id=lp.product_id')
    ->where('pd.language_id=' . (int)$this->config->get('config_language_id') . ' AND lp.product_id in (' . $product_ids . ')')
    ->field('l.logcenter_name, pd.name')->select();

    return $result;
  }

  public function batchSetProducts($product_ids, $enable, $logcenter_id) {
    if($enable == 0 || $enable == 1) {
      $lgiProduct = M('logcenter_product');
      $map['product_id']  = array('in', $product_ids);
      $lgiProduct->where($map)->delete();
      if($enable == 0){
        foreach ($product_ids as $pid) {
          $lgiProduct->data(array('product_id'=>$pid, 'status'=>$enable, 'logcenter_id'=>$logcenter_id))->add();
        }
      }
    }
  }
  //lgie
}